{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['高一1班', '高一2班']\n",
      "896\n",
      "21人\n",
      "29人\n",
      "22人\n",
      "32人\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "import shutil\n",
    "import os\n",
    "\n",
    "cla = range(1, 21)\n",
    "banji = [f'高一{x}班' for x in cla]\n",
    "print(banji)\n",
    "\n",
    "target_dir = './dst'\n",
    "if os.path.exists(target_dir):\n",
    "    shutil.rmtree(target_dir)\n",
    "os.mkdir(target_dir)\n",
    "\n",
    "\n",
    "df = pd.read_excel('aa.xlsx', sheet_name=0, header=0, converters={\n",
    "                   0: str, 1: str, 3: str, 4: str, 6: str})\n",
    "\n",
    "print(df.shape[0])\n",
    "def write_to_xlsx(name, df):\n",
    "    wb = Workbook()\n",
    "    ws = wb.active\n",
    "    # head = ['年级编号','班级编号','班级名称','学籍号','民族代码','姓名','性别','出生日期','家庭地址','身高','体重','BMI得分','肺活量','肺活量得分','50米跑','50米跑得分','立定跳远','立定跳远得分','坐位体前屈','坐位体前屈得分','800米跑','800米跑得分','1000米跑','1000米跑得分','一分钟跳绳','一分钟跳绳得分','一分钟仰卧起坐','一分钟仰卧起坐得分','50米×8往返跑','50米×8往返跑得分','引体向上','引体向上得分','左眼视力','右眼视力','附加分','总分']\n",
    "    for r in dataframe_to_rows(df, index=False, header=True):\n",
    "        r[3] = str(r[3])\n",
    "        ws.append(r)\n",
    "    count = df.shape[0]\n",
    "    print(f'{count}人')\n",
    "    wb.save(f'{name}({count}人).xlsx')\n",
    "\n",
    "\n",
    "for b in banji:\n",
    "    bdf = df[df['班级名称'] == b]\n",
    "    male = bdf[bdf['性别'] == '1']\n",
    "    female = bdf[bdf['性别'] == '2']\n",
    "    write_to_xlsx(f'{target_dir}/{b}-男生', male)\n",
    "    write_to_xlsx(f'{target_dir}/{b}-女生', female)\n",
    "    # print(bdf[bdf['性别'] == 1])\n",
    "    # print(bdf[bdf['性别'] == 2])\n"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 64-bit",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
